class Norm < ActiveRecord::Base
  belongs_to :survey
  belongs_to :item

  validates_presence_of :explanation
  validates_presence_of :begin_num
  validates_presence_of :end_num

  validates_numericality_of :begin_num, :allow_nil => true
  validates_numericality_of :end_num  , :allow_nil => true
  validates_numericality_of :age_start, :allow_nil => true
  validates_numericality_of :age_end, :allow_nil => true
  validates_numericality_of :specimen_avg, :allow_nil => true
  validates_numericality_of :specimen_standard_deviation, :allow_nil => true
  validates_numericality_of :specimen_num, :only_integer => true, :allow_nil => true

  #根据用户特征查找符合他的常模
  def self.find_by_curr_user(test_id, curr_user)
    Norm.find(:all, :conditions => 
        [" survey_id = ? and age_end >= ? and age_start <= ?  and sex = ? ",
          test_id, curr_user.age, curr_user.age, curr_user.gender
        ])
  end

  #生成常模的交叉列表
  def self.cross_table(test_id)

    item_sql = "SELECT DISTINCT item_id FROM norms where survey_id = " + test_id
    @items = Norm.find_by_sql(item_sql)

    sql = ""
    for item in @items
      sql += "CONCAT(SUM(IF(item_id='" + item.item_id.to_s + "', specimen_avg, 0)), ',', "
      sql += "SUM(IF(item_id='" + item.item_id.to_s + "', specimen_standard_deviation, 0))) as item" + item.item_id.to_s + ", "
    end

    sql = "SELECT CONCAT(age_start, ' - ', age_end, '岁') age, sex, " + sql
    sql += " specimen_num  FROM norms where survey_id = " + test_id
    sql += " GROUP BY age_start, age_end, sex;"

    #常模考察项目不细分的情况
    if Survey.find(test_id).norm_info.consider_item == NORM_ITEM_TYPE_NUM
      sql = "SELECT CONCAT(age_start, ' - ', age_end, '岁') age, sex, "
      sql += "CONCAT(specimen_avg,',', specimen_standard_deviation) as item, specimen_num "
      sql += "FROM norms where survey_id = " + test_id + " GROUP BY age_start, age_end, sex;"
    end
    @norms = Norm.find_by_sql(sql)
  end
  
end
